<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Tifyjsgl extends Common
{

	//列表--非常规和进场
    public function index()
    {

        $where = [];
        $where['is_lock'] = 1;
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $sn = $this->request->get('sn');
        $this->assign('sn',$sn);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $wdmc = $this->request->get('wdmc');
        $this->assign('wdmc',$wdmc);
        $pp = $this->request->get('pp');
        $this->assign('pp',$pp);
        $sqlx = $this->request->get('sqlx');
        $this->assign('sqlx',$sqlx);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);

        if($start&&$end){
            $where['t.ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.ctime'] = ['<',strtotime($end)];
        }
        if($sn){
            $where['t.sn'] = ['like',"%{$sn}%"];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxsids];
        }
        if($wdmc){
            $wdids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t2.net_id'] = ['in',$wdids];
        }
        if($pp){
//            echo db('pro_brands')->getLastSql();
            $where['t2.brand'] = [['exp','is null'],['in',$pp],'or'];
            $plids = db('pro_category')->where('brand_id',$pp)->column('id');
            $where['t2.category'] = [['exp','is null'],['in',$plids],'or'];
            $xlids = db('pro_series')->where('category_id','in',$plids)->column('id');
            $where['t2.series'] = [['exp','is null'],['in',$xlids],'or'];
            $skuids = db('pro_sku')->where('series_id','in',$xlids)->column('id');
            $where['t2.sku'] = [['exp','is null'],['in',$skuids],'or'];
            $pro_ids = db('pro_products')->where('sku_id','in',$skuids)->column('id');
            $where['t2.product'] = [['exp','is null'],['in',$pro_ids],'or'];
            $where['t2.tm_id'] = [['exp','is null'],['in',$pro_ids],'or'];
        }
        if($sqlx!=null){
            $where['t.type_id'] = $sqlx;
        }
        if($sqr){
            $emids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.cuser'] = ['in',$emids];
        }

        $count = db('cg_ti t')
            ->join('cg_ti_list t2','t.id=t2.ti_id')
            ->where($where)
            ->count();
        $data = db('cg_ti t')
            ->join('cg_ti_list t2','t.id=t2.ti_id')
            ->where($where)
            ->field('t.sn,t2.sn mxsn,t2.id,t.id ydid,t.type_id,t.cate_id,t.jxs_id,t2.net_id,t2.brand,t2.category,t2.series,t2.sku,t2.product,t2.tm_id,t2.feetype,t2.pizhunhanshui,t2.hx_yihexiao2,t.cuser,t.ctime,t2.sdrq,t2.jrbm')
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['type_title'] = db('cg_ti_type')->where('id',$v['type_id'])->value('title');
            $v['cate_title'] = db('cg_ti_cate')->where('id',$v['cate_id'])->value('title');
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            if($v['brand']){
                $v['brand'] = db('pro_brands')->where('id',$v['brand'])->value('title');
            }elseif($v['category']){
                $v['brand'] = db('pro_brands t')->join('pro_category t2','t2.brand_id=t.id')->where('t2.id',$v['category'])->value('t.brand_name');
            }elseif($v['series']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->where('t3.id',$v['series'])->value('t.brand_name');
            }elseif($v['sku']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->where('t4.id',$v['sku'])->value('t.brand_name');
            }elseif($v['product']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['product'])->value('t.brand_name');
            }elseif($v['tm_id']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['tm_id'])->value('t.brand_name');
            }
            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['fye'] = $v['pizhunhanshui'];
            $v['whxje'] = $v['pizhunhanshui']-$v['hx_yihexiao2'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['ctime']);
            if($v['sdrq']){
                $v['sdrq'] = date('Y-m-d',$v['sdrq']);
            }
            $data[$k] = $v;
        }

        $this->assign('data',$data);
        $this->assign('page',$page);

        $types = db('cg_ti_type')->select();
        $this->assign('types',$types);
        return $this->fetch();
    }

    //导出excel--非常规和进场
    public function excel(){
        //获取数据
        $where = [];
        $where['is_lock'] = 1;
        $start = $this->request->get('start');
        $end = $this->request->get('end');
        $sn = $this->request->get('sn');
        $jxsmc = $this->request->get('jxsmc');
        $wdmc = $this->request->get('wdmc');
        $pp = $this->request->get('pp');
        $sqlx = $this->request->get('sqlx');
        $sqr = $this->request->get('sqr');

        if($start&&$end){
            $where['t.ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.ctime'] = ['<',strtotime($end)];
        }
        if($sn){
            $where['t.sn'] = ['like',"%{$sn}%"];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxsids];
        }
        if($wdmc){
            $wdids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t2.net_id'] = ['in',$wdids];
        }
        if($pp){
//            echo db('pro_brands')->getLastSql();
            $where['t2.brand'] = [['exp','is null'],['in',$pp],'or'];
            $plids = db('pro_category')->where('brand_id',$pp)->column('id');
            $where['t2.category'] = [['exp','is null'],['in',$plids],'or'];
            $xlids = db('pro_series')->where('category_id','in',$plids)->column('id');
            $where['t2.series'] = [['exp','is null'],['in',$xlids],'or'];
            $skuids = db('pro_sku')->where('series_id','in',$xlids)->column('id');
            $where['t2.sku'] = [['exp','is null'],['in',$skuids],'or'];
            $pro_ids = db('pro_products')->where('sku_id','in',$skuids)->column('id');
            $where['t2.product'] = [['exp','is null'],['in',$pro_ids],'or'];
            $where['t2.tm_id'] = [['exp','is null'],['in',$pro_ids],'or'];
        }
        if($sqlx!=null){
            $where['t.type_id'] = $sqlx;
        }
        if($sqr){
            $emids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.cuser'] = ['in',$emids];
        }

        $data = db('cg_ti t')
            ->join('cg_ti_list t2','t.id=t2.ti_id')
            ->where($where)
            ->field('t.sn,t2.sn mxsn,t2.id,t.id ydid,t.type_id,t.cate_id,t.jxs_id,t2.net_id,t2.brand,t2.category,t2.series,t2.sku,t2.product,t2.tm_id,t2.feetype,t2.pizhunhanshui,t2.hx_yihexiao2,t.cuser,t.ctime,t2.sdrq,t2.jrbm')
            ->select();
//        echo Db::table('cg_ti')->getLastSql();
        foreach($data as $k=>$v){
            $v['type_title'] = db('cg_ti_type')->where('id',$v['type_id'])->value('title');
            $v['cate_title'] = db('cg_ti_cate')->where('id',$v['cate_id'])->value('title');
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            if($v['brand']){
                $v['brand'] = db('pro_brands')->where('id',$v['brand'])->value('title');
            }elseif($v['category']){
                $v['brand'] = db('pro_brands t')->join('pro_category t2','t2.brand_id=t.id')->where('t2.id',$v['category'])->value('t.brand_name');
            }elseif($v['series']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->where('t3.id',$v['series'])->value('t.brand_name');
            }elseif($v['sku']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->where('t4.id',$v['sku'])->value('t.brand_name');
            }elseif($v['product']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['product'])->value('t.brand_name');
            }elseif($v['tm_id']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['tm_id'])->value('t.brand_name');
            }
            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['fye'] = $v['pizhunhanshui'];
            $v['whxje'] = $v['pizhunhanshui']-$v['hx_yihexiao2'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['ctime']);
            if($v['sdrq']){
                $v['sdrq'] = date('Y-m-d',$v['sdrq']);
            }
            $data[$k] = $v;
        }

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("TI费用解锁管理-非常规和进场");
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->mergeCells('A1:M1');
        $as->setCellValue('A1','TI费用解锁管理-非常规和进场');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:M2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //标题
        $as->getStyle('A3:M3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A3:M3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->setCellValue('A3','序号');
        $as->setCellValue('B3','编号');
        $as->setCellValue('C3','明细编号');
        $as->setCellValue('D3','申请类型');
        $as->setCellValue('E3','经销商名称');
        $as->setCellValue('F3','网点名称');
        $as->setCellValue('G3','品牌');
        $as->setCellValue('H3','三级费用科目');
        $as->setCellValue('I3','费用额');
        $as->setCellValue('J3','已核销金额');
        $as->setCellValue('K3','未核销金额');
        $as->setCellValue('L3','申请人');
        $as->setCellValue('M3','锁定日期');

        foreach($data as $k=>$v){
            $n = 4+$k;
            $as->setCellValue('A'.$n,$k+1);
            $as->setCellValue('B'.$n,''.$v['sn']);
            $as->setCellValue('C'.$n,$v['mxsn'].'');
            $as->setCellValue('D'.$n,$v['type_title'].'-'.$v['cate_title']);
            $as->setCellValue('E'.$n,$v['jxs_title']);
            $as->setCellValue('F'.$n,$v['net_title']);
            $as->setCellValue('G'.$n,$v['brand']);
            $as->setCellValue('H'.$n,$v['sjfykm']);
            $as->setCellValue('I'.$n,$v['fye']);
            $as->setCellValue('J'.$n,$v['hx_yihexiao2']);
            $as->setCellValue('K'.$n,$v['whxje']);
            $as->setCellValue('L'.$n,$v['cuser']);
            $as->setCellValue('M'.$n,$v['sdrq']);
        }

        $n = $as->getHighestRow();
        $as->getStyle('A1:M'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=TI费用解锁管理-非常规和进场.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    //列表--常规
    public function cgindex()
    {
        $where = [];
        $where['is_lock'] = 1;
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $sn = $this->request->get('sn');
        $this->assign('sn',$sn);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $wdmc = $this->request->get('wdmc');
        $this->assign('wdmc',$wdmc);
        $pp = $this->request->get('pp');
        $this->assign('pp',$pp);
        $sqlx = $this->request->get('sqlx');
        $this->assign('sqlx',$sqlx);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);

        if($start&&$end){
            $where['t.created_on'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.created_on'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.created_on'] = ['<',strtotime($end)];
        }
        if($sn){
            $where['t.sn'] = ['like',"%{$sn}%"];
        }
        if($jxsmc){
            $jxs_ids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxs_ids];
        }
        if($wdmc){
            $net_ids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t2.net_id'] = ['in',$net_ids];
        }
        if($pp){
            $brand_ids = db('pro_products t')
                ->join('pro_sku t2','t2.id=t.sku_id')
                ->join('pro_series t3','t3.id=t2.series_id')
                ->join('pro_category t4','t4.id=t3.category_id')
                ->join('pro_brands t5','t5.id=t4.brand_id')
                ->where('brand_name','like',"%{$pp}%")
                ->column('t.id');
            $where['t2.pro_id'] = ['in',$brand_ids];
        }
        if($sqr){
            $sqr_ids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.c_user_id'] = ['in',$sqr_ids];
        }

        $count = Db::table('ti_apply t')
            ->join('ti_apply_detail t2','t2.apply_id=t.id')
            ->where($where)->count();
        $data = Db::table('ti_apply t')
            ->join('ti_apply_detail t2','t2.apply_id=t.id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.no sn,t2.detail_no,t.id ydid,t.jxs_id,t2.net_id,t2.pro_id,t2.feiyong_id,t2.pifuzonge,t2.hx_yihexiao2,t.c_users_id,t2.sdrq,t2.fankuijine fye')
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        $heji_yhx=$heji_whx=0;
        foreach($data as $k=>$v){
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');

            $v['brand'] = db('pro_brands t')
                ->join('pro_category t2','t2.brand_id=t.id')
                ->join('pro_series t3','t3.category_id=t2.id')
                ->join('pro_sku t4','t4.series_id=t3.id')
                ->join('pro_products t5','t5.id=t4.id')
                ->where('t5.id',$v['pro_id'])->value('t.brand_name');

            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feiyong_id'])->value('title');
            $v['whxje'] = $v['fye']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['sqr'])->value('em_name');
            $v['ctime'] = $v['created_on'];
            $data[$k] = $v;
        }

        $this->assign('heji_yhx',$heji_yhx);
        $this->assign('heji_whx',$heji_whx);
        $this->assign('data',$data);
        $this->assign('page',$page);
        $types = db('cg_ti_type')->select();
        $this->assign('types',$types);
        return $this->fetch();
    }

    //导出excel--常规
    public function cgexcel(){
        //获取数据
        $where = [];
        $where['is_lock'] = 1;
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $sn = $this->request->get('sn');
        $this->assign('sn',$sn);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $wdmc = $this->request->get('wdmc');
        $this->assign('wdmc',$wdmc);
        $pp = $this->request->get('pp');
        $this->assign('pp',$pp);
        $sqlx = $this->request->get('sqlx');
        $this->assign('sqlx',$sqlx);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);

        if($start&&$end){
            $where['t.created_on'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.created_on'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.created_on'] = ['<',strtotime($end)];
        }
        if($sn){
            $where['t.sn'] = ['like',"%{$sn}%"];
        }
        if($jxsmc){
            $jxs_ids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxs_ids];
        }
        if($wdmc){
            $net_ids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t2.net_id'] = ['in',$net_ids];
        }
        if($pp){
            $brand_ids = db('pro_products t')
                ->join('pro_sku t2','t2.id=t.sku_id')
                ->join('pro_series t3','t3.id=t2.series_id')
                ->join('pro_category t4','t4.id=t3.category_id')
                ->join('pro_brands t5','t5.id=t4.brand_id')
                ->where('brand_name','like',"%{$pp}%")
                ->column('t.id');
            $where['t2.pro_id'] = ['in',$brand_ids];
        }
        if($sqr){
            $sqr_ids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.c_user_id'] = ['in',$sqr_ids];
        }

        $count = Db::table('ti_apply t')
            ->join('ti_apply_detail t2','t2.apply_id=t.id')
            ->where($where)->count();
        $data = Db::table('ti_apply t')
            ->join('ti_apply_detail t2','t2.apply_id=t.id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.no sn,t2.detail_no,t.id ydid,t.jxs_id,t2.net_id,t2.pro_id,t2.feiyong_id,t2.pifuzonge,t2.hx_yihexiao2,t.c_users_id,t2.sdrq,t2.fankuijine fye')
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        $heji_yhx=$heji_whx=0;
        foreach($data as $k=>$v){
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');

            $v['brand'] = db('pro_brands t')
                ->join('pro_category t2','t2.brand_id=t.id')
                ->join('pro_series t3','t3.category_id=t2.id')
                ->join('pro_sku t4','t4.series_id=t3.id')
                ->join('pro_products t5','t5.id=t4.id')
                ->where('t5.id',$v['pro_id'])->value('t.brand_name');

            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feiyong_id'])->value('title');
            $v['whxje'] = $v['fye']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['sqr'])->value('em_name');
            $v['ctime'] = $v['created_on'];
            $data[$k] = $v;
        }

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("TI费用解锁管理-常规");
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->mergeCells('A1:M1');
        $as->setCellValue('A1','TI费用解锁管理-常规');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:M2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //标题
        $as->getStyle('A3:M3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A3:M3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->setCellValue('A3','序号');
        $as->setCellValue('B3','编号');
        $as->setCellValue('C3','明细编号');
        $as->setCellValue('D3','申请类型');
        $as->setCellValue('E3','经销商名称');
        $as->setCellValue('F3','网点名称');
        $as->setCellValue('G3','品牌');
        $as->setCellValue('H3','三级费用科目');
        $as->setCellValue('I3','费用额');
        $as->setCellValue('J3','已核销金额');
        $as->setCellValue('K3','未核销金额');
        $as->setCellValue('L3','申请人');
        $as->setCellValue('M3','锁定日期');

        foreach($data as $k=>$v){
            $n = 4+$k;
            $as->setCellValue('A'.$n,$k+1);
            $as->setCellValue('B'.$n,''.$v['sn']);
            $as->setCellValue('C'.$n,$v['mxsn'].'');
            $as->setCellValue('D'.$n,$v['type_title'].'-'.$v['cate_title']);
            $as->setCellValue('E'.$n,$v['jxs_title']);
            $as->setCellValue('F'.$n,$v['net_title']);
            $as->setCellValue('G'.$n,$v['brand']);
            $as->setCellValue('H'.$n,$v['sjfykm']);
            $as->setCellValue('I'.$n,$v['fye']);
            $as->setCellValue('J'.$n,$v['hx_yihexiao2']);
            $as->setCellValue('K'.$n,$v['whxje']);
            $as->setCellValue('L'.$n,$v['cuser']);
            $as->setCellValue('M'.$n,$v['sdrq']);
        }

        $n = $as->getHighestRow();
        $as->getStyle('A1:M'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=TI费用解锁管理-常规.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');

    }

    //解锁-非常规
    public function unlock(){
        $ids = $this->request->post('ids/a');
        $bool = db('cg_ti_list')->where('id','in',$ids)->update(['is_lock'=>0]);
        if(false!==$bool){
            echo json_encode(['success'=>true,'msg'=>'ok']);die;
        }else{
            echo json_encode(['success'=>false,'msg'=>'error']);die;
        }
    }

    //解锁-常规
    public function cgunlock(){
        $ids = $this->request->post('ids/a');
        $bool = db('ti_apply_detail')->where('id','in',$ids)->update(['is_lock'=>0]);
        if(false!==$bool){
            echo json_encode(['success'=>true,'msg'=>'ok']);die;
        }else{
            echo json_encode(['success'=>false,'msg'=>'error']);die;
        }
    }
}
